package com.bz.xtcx.manager.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.mapping.StatementType;

import com.bz.xtcx.manager.entity.BusUserForm;
import com.bz.xtcx.manager.mapper.provider.BusUserFormHisProvider;
import com.bz.xtcx.manager.vo.VoQuery;
import com.bz.xtcx.manager.vo.VoTaskSum;

public interface BusUserFormHisMapper {
    
    @Insert("insert into `bus_user_form_his`(id, user_id, res_code, name, code, re_name, form_type, detail, check_status, status, creater, name_alias, code_alias)"
            + " VALUES(#{id, jdbcType=VARCHAR},"
            + " #{userId, jdbcType=VARCHAR},"
            + " #{number, jdbcType=VARCHAR},"
            + " #{name, jdbcType=VARCHAR},"
            + " #{code, jdbcType=VARCHAR},"
            + " #{reName, jdbcType=VARCHAR},"
            + " #{formType, jdbcType=VARCHAR},"
            + " #{detail, jdbcType=VARCHAR},"
            + " #{checkStatus, jdbcType=INTEGER},"
            + " #{status, jdbcType=INTEGER},"
            + " #{creater, jdbcType=VARCHAR},"
            + " #{nameAlias, jdbcType=VARCHAR},"
            + " #{codeAlias, jdbcType=VARCHAR}" +
            ")"
            )
    @SelectKey(before = true, keyProperty = "id", resultType = String.class, statementType = StatementType.STATEMENT, statement="select uuid()")
    int insert(BusUserForm form);
    
    @Delete("delete from `bus_user_form_his` where id = #{id}")
    int del(String id);
    
    @Update("update `bus_user_form_his` set name=#{name, jdbcType=VARCHAR},"
            + " code=#{code, jdbcType=VARCHAR},"
            + " res_code=#{number, jdbcType=VARCHAR},"
            + " re_name=#{reName, jdbcType=VARCHAR},"
            + " detail=#{detail, jdbcType=VARCHAR},"
            + " status=#{status, jdbcType=INTEGER},"
            + " updater=#{updater, jdbcType=VARCHAR},"
            + " name_alias=#{nameAlias},"
            + " code_alias=#{codeAlias},"
            + " check_status=#{checkStatus, jdbcType=INTEGER}"
            + " where id=#{id}")
    int update(BusUserForm form);
    
    @Update("update `bus_user_form_his` set check_status=#{check, jdbcType=INTEGER} where id=#{id}")
    int updateCheck(@Param("check")int check, @Param("id")String id);
    
    
    @Select("select * from `bus_user_form_his` where form_type = #{formType} and user_id = #{userId} and check_status = #{check}")
    @Results(
        id = "busUserFormHis",
        value = {
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "userId", column = "user_id"),
            @Result(property = "number", column = "res_code"),
            @Result(property = "formType", column = "form_type"),
            @Result(property = "name", column = "name"),
            @Result(property = "code", column = "code"),
            @Result(property = "reName", column = "re_name"),
            @Result(property = "detail", column = "detail"),
            @Result(property = "checkStatus", column = "check_status"),
            @Result(property = "status", column = "status"),
            @Result(property = "creater", column = "creater"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updater", column = "updater"),
            @Result(property = "updateTime", column = "update_time"),
            @Result(property = "info", column = "info"),
            @Result(property = "checker", column = "checker"),
            @Result(property = "checkTime", column = "check_time")
        }
    )
    BusUserForm findByUserIdAndCheck(@Param("formType")int formType, @Param("userId")String userId, @Param("check")int check);
    
    @Select("select * from `bus_user_form_his` where user_id=#{userId} "
            + "and form_type = (select user_type from `bus_user` where user_id=#{userId}) order by create_time desc limit 1")
    @ResultMap("busUserFormHis")
    BusUserForm findByUserId(String userId);
    
    @Select("select * from `bus_user_form_his` where id=#{id}")
    @ResultMap("busUserFormHis")
    BusUserForm findById(String id);
    
    //@Select("select * from `bus_user_form_his` f left join `bus_user` u on f.user_id=u.user_id where f.form_type=#{type} and f.check_status=#{check}")
    @SelectProvider(type = BusUserFormHisProvider.class, method = "findByCondition")
    @ResultMap("busUserFormHis")
    List<BusUserForm> findByFromType(VoQuery e);
    
    @Select("select * from `bus_user_form_his` where re_name=#{reName} and check_status>-1")
    @ResultMap("busUserFormHis")
    List<BusUserForm> findByReName(String reName);
    
    @Select("select * from `bus_user_form_his` where code=#{code} and check_status>-1")
    @ResultMap("busUserFormHis")
    List<BusUserForm> findByCode(String code);

    @Select("select * from `bus_user_form_his` where code=#{code} and form_type = #{type} and check_status>-1")
    @ResultMap("busUserFormHis")
    List<BusUserForm> findByCodeAndType(@Param("code")String code, @Param("type") int type);

    @Select("select form_type, count(*) count from `bus_user_form_his` where check_status=#{check} group by form_type "
            + "union "
            + "select 8 form_type,count(*) count from `cms_exchanges` where check_status = 0 "
            + "union "
            + "select 9 form_type,count(*) count from `cms_content` where check_status = 0 "
            + "order by form_type")
    @Results(
        value = {
                @Result(property = "formType", column = "form_type"),
                @Result(property = "count", column = "count"),
            }
        )
    List<VoTaskSum> findGroupByFromType(int check);
    
//    @Select("select a.form_type, a.check_status, count(*) count , b.info, b.check_time from `bus_user_form_his` a "
//            + "left join (select form_id,info,max(check_time) check_time from  bus_user_form_check group by form_id) b on a.id=b.form_id "
//            + "where user_id = #{userId} group by form_type, a.check_status")
//    @Select("select a.form_type form_type,a.check_status check_status,a.count count,b.info info, b.check_time check_time from "
//            + " (select id, form_type, check_status, count(*) count from `bus_user_form_his` "
//            + " where user_id = #{userId} "
//            + " group by form_type, check_status, id) a left join "
//            + " (select x.form_id form_id, x.info info, y.check_time check_time from bus_user_form_check x inner join "
//            + " (select form_id,info,max(check_time) check_time from  bus_user_form_check group by form_id) y "
//            + " on x.form_id=y.form_id and x.check_time=y.check_time) b on a.id=b.form_id" )
    @Select("select a.form_type form_type,a.check_status check_status,a.count count,b.info info, b.check_time from "
            + " (select e.id id, e.form_type form_type, e.check_status check_status, count(*) count from `bus_user_form_his` e inner join bus_user f "
            + " on e.user_id = f.user_id and e.name = f.name and e.code=f.id_number "
            + " where e.user_id = #{userId} "
            + " group by e.form_type, e.check_status, e.id) a left join "
            + " (select x.form_id form_id, x.info info, y.check_time check_time from bus_user_form_check x inner join "
            + " (select form_id,info,max(check_time) check_time from  bus_user_form_check group by form_id) y "
            + " on x.form_id=y.form_id and x.check_time=y.check_time) b on a.id=b.form_id" )
    List<VoTaskSum> findSubmitLib(@Param("userId")String userId);
}
